﻿import pandas as pd
from sqlalchemy import create_engine
from datetime import datetime
import pyodbc


engine2 = create_engine(
    "mssql+pyodbc://pereader:pereader@172.18.65.31:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
    fast_executemany=True)

# engine = create_engine(
#     "mssql+pyodbc://qatest:qatest@192.168.3.236:1433/SortingDB?driver=ODBC+Driver+17+for+SQL+Server",
#     fast_executemany=True)

# 测试用
# 显示所有的列 方便调试
pd.set_option('display.max_columns', None)


def upload_csv(clas, user_id, test_data, moid, glue, logger):
    conn = pyodbc.connect(
        DRIVER='{SQL SERVER}',
        # SERVER='172.18.65.31',
        # DATABASE='QualityDB',
        # uid='qains',
        # pwd='qains8100'
        SERVER='192.168.3.236',
        DATABASE='SortingDB',
        uid='qatest',
        pwd='qatest'
    )
    list1 = test_data
    cursor = conn.cursor()
    if clas == '固晶推力':
        try:
            list_save(list1[0], cursor, user_id, moid, glue, '固晶推力')
            # i.to_sql(name='Chip_shear', con=engine, if_exists='append', index=False)
        except Exception as e:
            logger.error(e)
            conn.close()
            return False, e
        else:
            conn.commit()
            conn.close()
            return True, None
    # 已修改
    elif clas == '焊线推力':
        try:
            list_save(list1[0], cursor, user_id, moid, glue, '焊线推力_P')
            list_save(list1[1], cursor, user_id, moid, glue, '焊线推力_N')
            list_save(list1[2], cursor, user_id, moid, glue, '焊线推力_B')
        except Exception as e:
            logger.error(e)
            conn.close()
            return False, e
        else:
            conn.commit()
            conn.close()
            return True, None
    elif clas == '焊线推力-齐纳':
        try:
            list_save(list1[0], cursor, user_id, moid, glue, '焊线推力_P')
            list_save(list1[1], cursor, user_id, moid, glue, '焊线推力_N')
            list_save(list1[2], cursor, user_id, moid, glue, '焊线推力_B')
            list_save(list1[3], cursor, user_id, moid, glue, '焊线推力_QN_P')
            list_save(list1[4], cursor, user_id, moid, glue, '焊线推力_QN_N')
        except Exception as e:
            logger.error(e)
            conn.close()
            return False, e
        else:
            conn.commit()
            conn.close()
            return True, None
    elif clas == '焊线推力-倒装':
        try:
            print(list1[0])
            list_save(list1[0], cursor, user_id, moid, glue, '焊线推力_P')
            list_save(list1[1], cursor, user_id, moid, glue, '焊线推力_N')
        except Exception as e:
            logger.error(e)
            conn.close()
            return False, e
        else:
            conn.commit()
            conn.close()
            return True, None
    elif clas == '焊线拉力':
        try:
            list_save(list1[0], cursor, user_id, moid, glue, '焊线拉力_chip')
            list_save(list1[1], cursor, user_id, moid, glue, '焊线拉力_B')
        except Exception as e:
            logger.error(e)
            print(e)
            conn.close()
            return False, e
        else:
            conn.commit()
            conn.close()
            return True, None
    elif clas == '焊线拉力-齐纳':
        try:
            list_save(list1[0], cursor, user_id, moid, glue, '焊线拉力_chip')
            list_save(list1[1], cursor, user_id, moid, glue, '焊线拉力_B')
            list_save(list1[2], cursor, user_id, moid, glue, '焊线拉力_QN')
        except Exception as e:
            logger.error(e)
            conn.close()
            return False, e
        else:
            conn.commit()
            conn.close()
            return True, None
    elif clas == '焊线拉力-倒装':
        try:
            list_save(list1[0], cursor, user_id, moid, glue, '焊线拉力_chip')
        except Exception as e:
            logger.error(e)
            conn.close()
            return False, e
        else:
            conn.commit()
            conn.close()
            return True, None
    else:
        return False, '未找到对应的数据文件类型'


def get_moid_info(moid):
    df1 = pd.read_sql(f"SELECT ITEM_DESCRIPTION FROM MO_BOM WHERE DOC_NO='{moid}' and "
                      f"ITEM_DESCRIPTION LIKE '%金线'", engine2)
    Wire_diameter = None if df1.empty else df1.loc[0, 'ITEM_DESCRIPTION']
    df2 = pd.read_sql(f"SELECT CONVERT(VARCHAR(2), [宽mil])+CONVERT(VARCHAR(2), [长mil]) AS chip_size from "
                      f"MO_CHIP_VALUE WHERE DOC_NO='{moid}'", engine2)
    chip_size = None if df2.empty else df2.loc[0, 'chip_size']
    df3 = pd.read_sql(f"SELECT ITEM_DESCRIPTION FROM MO_BOM WHERE DOC_NO='{moid}' and "
                      f"ITEM_DESCRIPTION LIKE '%固晶胶'", engine2)
    DB_glue = None if df3.empty else df3.loc[0, 'ITEM_DESCRIPTION']
    return Wire_diameter, chip_size, DB_glue


def get_insert_sql(table_name, df):
    colnames = '[' + '],['.join(list(df.columns)) + ']'
    # print(colnames)
    values_lis = []

    def func1(sr):
        value_str = "("
        for value in list(sr.values):
            value = str(value)
            value = value.strip(' ')
            if value and value != 'None' and value != 'nan' and value != 'NaT':
                value_str = value_str + "'" + value + "',"
            else:
                value_str = value_str + 'Null' + ","
        value_str = value_str[: -1] + ")"
        values_lis.append(value_str)

    sql = ""
    df.apply(func1, axis=1)
    for i in range(0, len(values_lis) // 500 + 1):
        if len(values_lis) > (i + 1) * 500:
            values_str = ','.join(values_lis[i * 500: (i + 1) * 500])
        else:
            values_str = ','.join(values_lis[i * 500:])
        sql = sql + f"insert into {table_name} ({colnames}) values {values_str};"
    # values_str = ','.join(values_lis)
    # sql = f"insert into {self.table_name} ({colnames}) values {values_str};"
    # print(sql)
    # with open(main_filepath + r'\测试\sql.txt', 'w', encoding='utf-8') as f:
    #     f.write(sql)
    return sql


def list_save(list1, cursor, user_id, moid, glue, name):
    for i in list1:
        i['UpdateTime'] = datetime.now()
        i['UserID'] = user_id
        i['MoID'] = moid
        if glue:
            i['REMARK'] = glue
        i['MEASURE_ITEM'] = name
        sql = get_insert_sql('CTQ_push_pull_data_HX', i)
        cursor.execute(sql)


def get_mo_list():
    df = pd.read_sql("SELECT DISTINCT DOC_NO FROM MO_ITEM", engine2)
    list1 = list(df['DOC_NO'])
    return list1